import 'package:mysql1/mysql1.dart';

class DatabaseManager {
  MySqlConnection? _connection;

  Future<void> connect() async {
    try {
      final settings = ConnectionSettings(
        host: 'gateway01.ap-southeast-1.prod.aws.tidbcloud.com',
        port: 4000,
        user: '2eoagz2rTEiMGMb.root',
        password: 'R0T40GnEid6BBdaG',
        db: 'my_db',
        useSSL: true,
        ssl: {
          'ca': '/etc/ssl/certs/ca-certificates.crt',
        },
      );

      _connection = await MySqlConnection.connect(settings);
      print('数据库连接成功！');
    } catch (e) {
      print('连接失败: ${e.toString()}');
      rethrow;
    }
  }

  Future<bool> addRecord(String name, String message) async {
    try {
      await _connection?.query(
        'INSERT INTO test_table (name, message) VALUES (?, ?)',
        [name, message],
      );
      print('成功添加记录: $name, $message');
      return true;
    } catch (e) {
      print('添加记录失败: ${e.toString()}');
      return false;
    }
  }

  Future<List<Map<String, dynamic>>> getAllRecords() async {
    try {
      final results = await _connection?.query('SELECT * FROM test_table');
      return results?.map((row) => {
        'id': row[0],
        'name': row[1],
        'message': row[2],
      }).toList() ?? [];
    } catch (e) {
      print('获取记录失败: ${e.toString()}');
      return [];
    }
  }

  Future<Map<String, dynamic>?> getRecordById(int id) async {
    try {
      final results = await _connection?.query(
        'SELECT * FROM test_table WHERE id = ?',
        [id],
      );
      if (results != null && results.isNotEmpty) {
        final row = results.first;
        return {
          'id': row[0],
          'name': row[1],
          'message': row[2],
        };
      }
      return null;
    } catch (e) {
      print('获取记录失败: ${e.toString()}');
      return null;
    }
  }

  Future<bool> updateRecord(int id, {String? name, String? message}) async {
    try {
      final updates = <String>[];
      final params = <dynamic>[];
      
      if (name != null) {
        updates.add('name = ?');
        params.add(name);
      }
      if (message != null) {
        updates.add('message = ?');
        params.add(message);
      }
      
      if (updates.isEmpty) return false;
      
      params.add(id);
      await _connection?.query(
        'UPDATE test_table SET ${updates.join(', ')} WHERE id = ?',
        params,
      );
      print('成功更新ID为$id的记录');
      return true;
    } catch (e) {
      print('更新记录失败: ${e.toString()}');
      return false;
    }
  }

  Future<bool> deleteRecord(int id) async {
    try {
      await _connection?.query(
        'DELETE FROM test_table WHERE id = ?',
        [id],
      );
      print('成功删除ID为$id的记录');
      return true;
    } catch (e) {
      print('删除记录失败: ${e.toString()}');
      return false;
    }
  }

  Future<void> close() async {
    await _connection?.close();
    print('数据库连接已关闭');
  }
}